﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Forms;
using WpfMessageBoxLibrary;

namespace DataCombine
{

    public static class ExcelHelper
    {
        #region 私有方法

        /// <summary>
        /// 获取要保存的文件名称（含完整路径）
        /// </summary>
        /// <returns></returns>
        //private static string GetSaveFilePath()
        //{
        // SaveFileDialog saveFileDig = new SaveFileDialog();
        // saveFileDig.Filter = "Excel Office97-2003(*.xls)|.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
        // saveFileDig.FilterIndex = 0;
        // saveFileDig.OverwritePrompt = true;
        // saveFileDig.InitialDirectory = Common.DesktopDirectory;
        // string filePath = null;
        // if (saveFileDig.ShowDialog() == DialogResult.OK)
        // {
        // filePath = saveFileDig.FileName;
        // }

        // return filePath;
        //}

        /// <summary>
        /// 判断是否为兼容模式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private static bool GetIsCompatible(string filePath)
        {
            return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
        }



        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            else
            {
                return new XSSFWorkbook();
            }
        }

        /// <summary>
        /// 创建工作薄(依据文件流)
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        private static IWorkbook CreateWorkbook(bool isCompatible, Stream stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }

        /// <summary>
        /// 创建表格头单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            return style;
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    short format = cell.CellStyle.DataFormat;
                    if (format != 0) { 
                        return cell.DateCellValue.ToString("yyyy-MM-dd"); 
                    } else { 
                        return cell.NumericCellValue; 
                    }
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }

        /// <summary>
        /// 从工作表中生成DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
        {
            sheet = FillSheetMergedCells(sheet, headerRowIndex);

            DataTable table = new DataTable(sheet.SheetName);
           
            headerRowIndex = 0;

            IRow headerRow = sheet.GetRow(headerRowIndex);

            if(headerRow == null)
            {
                return table;
            }

            int cellCount = headerRow.LastCellNum;

            #region Header Row
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                //调用GetCellValue()函数
                ICell cells = headerRow.GetCell(i); //一个单元格
                string GCV = GetCellValue(cells);

                if (GCV == null || GCV.Trim() == "")
                {
                    GCV = "Empty Cell";
                }

                DataColumn column = new DataColumn(GCV.Trim());
                try
                {
                    table.Columns.Add(column);
                } 
                catch
                {
                    Console.WriteLine(string.Format("find duplicate column at s={0} c={1}", sheet.SheetName, i));
                    column.ColumnName += " c" + i.ToString();
                    table.Columns.Add(column);                   
                }                
            }
            #endregion

            #region Other Row
            for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                #region 行有效判断
                IRow row = sheet.GetRow(i);
                if(row == null)
                {
                    System.Windows.MessageBox.Show("发现空行 行号: " + i.ToString());
                    continue;
                }

                ICell cell = row.GetCell(0); //一个单元格
                if (cell == null)
                {
                    System.Windows.MessageBox.Show("发现非空行, 单行首为空单元格 行号: " + i.ToString());
                    continue;
                }

                string GC = GetCellValue(cell);
                if (string.IsNullOrEmpty(GC))
                {
                    //System.Windows.MessageBox.Show("发现非空行, 行首单元格仅有格式无内容 行号: " + i.ToString());
                    continue;
                }
                #endregion

                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    cell = row.GetCell(j);
                    #region method 1
                    /*
                    if (cell != null)
                    {                        
                        if (cell.CellType == CellType.Formula)
                        {
                            try
                            {
                                dataRow[j] = cell.NumericCellValue;
                            } 
                            catch
                            {
                                if (cell.ErrorCellValue == 7)
                                {
                                    dataRow[j] = "DIV/0";
                                    Console.WriteLine(string.Format("find DIV/0 at s={0} r={1} c={2}", sheet.SheetName, i, j));
                                }
                                else
                                {
                                    dataRow[j] = "Error";
                                }
                                        
                            }   
                        } 
                        else if (cell.CellType == CellType.Error)
                        {
                            dataRow[j] = @"CELL_ERROR";
                            Console.WriteLine(string.Format("find CELL_ERROR at s={0} r={1} c={2}", sheet.SheetName, i, j));
                        }
                        else
                        {
                            dataRow[j] = cell.ToString();
                        }
                    }
                    */
                    #endregion

                    #region method 2
                    var x = GetValueType(cell);

                    dataRow[j] = x;
                    #endregion
                }

                table.Rows.Add(dataRow);
                
            }

            #endregion

            return table;
        }

        private static int GetSheetRowCount(ISheet sheet)
        {
            int maxCellCount = 0;
            IRow row;
            for (int r = 0; r < sheet.LastRowNum; r++)
            {
                row = sheet.GetRow(r);
                if (row != null && row.LastCellNum > maxCellCount)
                {
                    maxCellCount = row.LastCellNum;
                }
            }

            return maxCellCount;
        }

        private static ISheet FillSheetMergedCells(ISheet sheet, int headerMergeRange)
        {
            Console.WriteLine("=====================sheet: " + sheet.SheetName.ToString());
            int maxCellCount = GetSheetRowCount(sheet);

            MessageBoxResult sheetIgnoreEmptyRowResult = MessageBoxResult.None;

            // method 2
            List<NPOI.SS.Util.CellRangeAddress> mgs = sheet.MergedRegions;
            foreach(NPOI.SS.Util.CellRangeAddress cra in mgs)
            {
                //Console.WriteLine("get a merge region:" + cra.NumberOfCells.ToString());
                //Console.WriteLine("merge region value: " + cra.FormatAsString());

                for (int c = cra.FirstColumn; c <= cra.LastColumn; c++)
                {
                    for (int r = cra.FirstRow; r <= cra.LastRow; r++)
                    {
                        ICell cell = sheet.GetRow(r).GetCell(c);
                        if(cell != null)
                        {
                            cell.SetCellValue(sheet.GetRow(cra.FirstRow).GetCell(cra.FirstColumn).ToString());
                        }
                        
                    }
                }
            }

            int emptyCellCount = 0;

            for (int r = 0; r < sheet.LastRowNum; r++)
            {
                IRow row = sheet.GetRow(r);

                if (row == null)
                {
                    row = sheet.CreateRow(r);
                }

                if (row != null)
                {
                    for (int c = 0; c < maxCellCount; c++)
                    {
                        ICell cell = row.GetCell(c);
                        if (cell == null)
                        {
                            
                            cell = row.CreateCell(c);
                        }

                        if (cell != null)
                        {
                            if (string.IsNullOrEmpty(cell.ToString()))
                            {
                                if (c == 0 && sheetIgnoreEmptyRowResult == MessageBoxResult.None)
                                {
                                    emptyCellCount++;
                                    if (emptyCellCount >= 5)
                                    {
                                        if(Properties.Settings.Default.ignoreEmptyRow == true)
                                        {
                                            return sheet;
                                        }
                                        else
                                        {
                                            var msgProperties = new WpfMessageBoxProperties()
                                            {
                                                Button = MessageBoxButton.OKCancel,
                                                ButtonOkText = "是(忽略)",
                                                CheckBoxText = "后面都按此处理",
                                                Image = MessageBoxImage.Exclamation,
                                                Header = "表格格式不规范",
                                                IsCheckBoxChecked = Properties.Settings.Default.ignoreEmptyRow,
                                                IsCheckBoxVisible = true,
                                                IsTextBoxVisible = false,
                                                Text = "发现这个表格连续5个行首为空, 是否忽略余下部分",
                                                Title = "表格格式不规范",
                                            };
                                            sheetIgnoreEmptyRowResult = WpfMessageBox.Show(ref msgProperties);
                                            //Console.WriteLine(result.ToString());
                                            bool checkBoxChecked = msgProperties.IsCheckBoxChecked;
                                            //Console.WriteLine(checkBoxChecked.ToString());
                                            Properties.Settings.Default.ignoreEmptyRow = checkBoxChecked;
                                            Properties.Settings.Default.Save();

                                            if (sheetIgnoreEmptyRowResult == MessageBoxResult.OK)
                                            {
                                                return sheet;
                                            }
                                        }
                                    }
                                }

                                cell.SetCellValue(string.Format("EC r{0} c{1}", r, c));
                            }
                        }
                    }
                }

            }
            return sheet; 
        }

        #endregion

        #region 公共导出方法

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        public static string ExportToExcel(DataSet sourceDs, string filePath)
        {

            if (string.IsNullOrEmpty(filePath))
            {
                return filePath;
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);

            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                DataTable table = sourceDs.Tables[i];
                string sheetName = "result" + i.ToString();
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = headerRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = cellStyle;
                }

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
                    }

                    rowIndex++;
                }
            }

            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            workbook = null;

            return filePath;

        }


        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        public static string ExportToExcel(DataTable sourceTable, string sheetName, string filePath)
        {
            if (sourceTable.Rows.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath))
            {
                return filePath;
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);

            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
            {
                ICell headerCell = headerRow.CreateCell(column.Ordinal);
                headerCell.SetCellValue(column.ColumnName);
                headerCell.CellStyle = cellStyle;
            }

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
                }

                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        /// <summary>
        /// 由List导出Excel
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="data">在导出的List</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName, string filePath) where T : class
        {
            if (data.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath))
            {
                return filePath;
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);

            for (int i = 0; i < headerNameList.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(headerNameList[i].Value);
                cell.CellStyle = cellStyle;
            }

            Type t = typeof(T);
            int rowIndex = 1;
            foreach (T item in data)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int n = 0; n < headerNameList.Count; n++)
                {
                    object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null);
                    dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());
                }
                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        /// <summary>
        /// 由DataGridView导出
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string ExportToExcel(DataGridView grid, string sheetName, string filePath)
        {
            if (grid.Rows.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath))
            {
                return filePath;
            }

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetCellStyle(workbook);
            ISheet sheet = workbook.CreateSheet(sheetName);

            IRow headerRow = sheet.CreateRow(0);

            for (int i = 0; i < grid.Columns.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(grid.Columns[i].Name);
                cell.CellStyle = cellStyle;
            }

            int rowIndex = 1;
            foreach (DataGridViewRow row in grid.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int n = 0; n < grid.Columns.Count; n++)
                {
                    dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
                }
                rowIndex++;
            }

            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        #endregion

        #region 公共服务方法

        public static List<string> GetSheetNames(string filePath)
        {
            List<string> sheetNames = new List<string>();

            bool isCompatible;
            IWorkbook wb;
            FileStream stream = null;

            try
            {
                
                stream = System.IO.File.OpenRead(filePath);
                isCompatible = GetIsCompatible(filePath);
                wb = CreateWorkbook(isCompatible, stream);

                for (int i = 0; i < wb.NumberOfSheets; i++)
                {
                    string sn = wb.GetSheetName(i);
                    sheetNames.Add(sn);
                }

                stream.Close();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("请关闭文件,再导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                }

            }


            return sheetNames;
        }

        public static List<string> GetSheetNames(IWorkbook wb)
        {
            List<string> sheetNames = new List<string>();

            for (int i = 0; i < wb.NumberOfSheets; i++)
            {
                sheetNames.Add(wb.GetSheetAt(i).SheetName);
            }

            return sheetNames;
        }


        #endregion

        #region 公共导入方法

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
        {
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }

            DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);

            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            FileStream stream = null;
            try
            {
                stream = System.IO.File.OpenRead(excelFilePath);
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible);
                
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("请关闭文件,再导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                DataTable DT = new DataTable();
                return DT;

            }
            finally
            {
                if(stream != null)
                {
                    stream.Close();
                }
                
            }

        }

        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
        {
            DataSet ds = new DataSet();
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
                ds.Tables.Add(table);
            }

            excelFileStream.Close();
            workbook = null;

            return ds;
        }

        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
        {
            FileStream stream = null;
            try
            {
                stream = System.IO.File.OpenRead(excelFilePath);
                
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, headerRowIndex, isCompatible);           
            }
            catch
            {
                System.Windows.MessageBox.Show("请关闭文件,再导入！", "提示", (MessageBoxButton)MessageBoxButtons.OK, (MessageBoxImage)MessageBoxIcon.Warning);
                DataSet ds = new DataSet();
                return ds;
            }
            finally
            {
                if(stream != null)
                {
                    stream.Close();
                }
            }
        }

        #endregion

        #region 公共转换方法

        /// <summary>
        /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列名，如第0列为A，第1列为B...</returns>
        public static string ConvertColumnIndexToColumnName(int index)
        {
            index = index + 1;
            int system = 26;
            char[] digArray = new char[100];
            int i = 0;
            while (index > 0)
            {
                int mod = index % system;
                if (mod == 0) mod = system;
                digArray[i++] = (char)(mod - 1 + 'A');
                index = (index - 1) / 26;
            }
            StringBuilder sb = new StringBuilder(i);
            for (int j = i - 1; j >= 0; j--)
            {
                sb.Append(digArray[j]);
            }
            return sb.ToString();
        }


        /// <summary>
        /// 转化日期
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public static DateTime ConvertDate(object date)
        {
            string dtStr = (date ?? "").ToString();

            DateTime dt = new DateTime();

            if (DateTime.TryParse(dtStr, out dt))
            {
                return dt;
            }

            try
            {
                string spStr = "";
                if (dtStr.Contains("-"))
                {
                    spStr = "-";
                }
                else if (dtStr.Contains("/"))
                {
                    spStr = "/";
                }
                string[] time = dtStr.Split(spStr.ToCharArray());
                int year = Convert.ToInt32(time[2]);
                int month = Convert.ToInt32(time[0]);
                int day = Convert.ToInt32(time[1]);
                string years = Convert.ToString(year);
                string months = Convert.ToString(month);
                string days = Convert.ToString(day);
                if (months.Length == 4)
                {
                    dt = Convert.ToDateTime(date);
                }
                else
                {
                    string rq = "";
                    if (years.Length == 1)
                    {
                        years = "0" + years;
                    }
                    if (months.Length == 1)
                    {
                        months = "0" + months;
                    }
                    if (days.Length == 1)
                    {
                        days = "0" + days;
                    }
                    rq = "20" + years + "-" + months + "-" + days;
                    dt = Convert.ToDateTime(rq);
                }
            }
            catch
            {
                throw new Exception("日期格式不正确，转换日期失败！");
            }
            return dt;
        }

        /// <summary>
        /// 转化数字
        /// </summary>
        /// <param name="d">数字字符串</param>
        /// <returns></returns>
        public static decimal ConvertDecimal(object d)
        {
            string dStr = (d ?? "").ToString();
            decimal result = 0;
            if (decimal.TryParse(dStr, out result))
            {
                return result;
            }
            else
            {
                throw new Exception("数字格式不正确，转换数字失败！");
            }

        }

        #endregion

        #region 数值转换string
        private static string GetCellValue(ICell cell)
        {
            /*
             * if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
                    return string.Empty;
                case CellType.Boolean: //bool类型
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric: //数字类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return cell.DateCellValue.ToString();
                    }
                    else //其它数字
                    {
                        return cell.NumericCellValue.ToString();
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();//
                case CellType.String: //string 类型
                    return cell.StringCellValue;
                case CellType.Formula: //带公式类型
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }*/
            //Console.WriteLine(cell.Sheet.SheetName + " row=" + cell.Row.ToString() + " col=" + cell.ColumnIndex.ToString());

            


            if (cell == null)
            {
                //Console.WriteLine("Empty Cell");
                return "Empty Cell";
            }
            else
            {
                if (cell.IsMergedCell)
                {
                    //Console.WriteLine("we find a merged cell");
                }

                //Console.WriteLine(cell.ToString());
                return cell.ToString();
            }
        }
        #endregion
    }
}
